CREATE PROCEDURE FW_SEQUENCES_RESET IS
    v_seq_name     varchar2(50);
    v_exist        number(11);
    v_used         number(11);
    t_num          number(11);
    t_sql          varchar2(500);
    v_cache        number(11);
    v_increment_by number(11);
    v_min_value    number(11);
    v_max_value    number(11);
    v_cycle        varchar2(20);

    --将发送报文的标识序列由数据库计数更新改为数据库序列
    CURSOR fw_sequences_list IS
        SELECT *
        FROM FW_SEQUENCES
        ORDER BY SEQ_NAME;

    --定义判断序列是否存在的的函数
    FUNCTION SEQ_IS_EXIST(i_seq_name VARCHAR2) RETURN NUMBER IS
        o_res_number number(1);
    BEGIN
        SELECT COUNT(0) INTO o_res_number FROM USER_SEQUENCES WHERE SEQUENCE_NAME = UPPER(i_seq_name);
        RETURN o_res_number;
    END;

BEGIN
    FOR fw_sequences IN fw_sequences_list
        LOOP
            -- 报文序列名称[BUSINESS_SEQ_001]
            v_seq_name := fw_sequences.SEQ_NAME;
            v_min_value := fw_sequences.SEQ_MIN_VALUE;
            v_max_value := fw_sequences.SEQ_MAX_VALUE;
            v_increment_by := fw_sequences.SEQ_INCREMENT_BY;
            v_cache := fw_sequences.SEQ_CACHE;
            v_cycle := fw_sequences.SEQ_CYCLE;

            -- 查询序列是否存在
            v_exist := SEQ_IS_EXIST(v_seq_name);

            IF v_exist > 0 THEN
                -- Cache为20，如果序列启用了则v_used至少为21
                SELECT LAST_NUMBER INTO v_used FROM USER_SEQUENCES WHERE SEQUENCE_NAME = UPPER(v_seq_name);
                IF v_used > 1 THEN

                    -- 序列存在则重置序列
                    EXECUTE IMMEDIATE 'SELECT ' || v_seq_name || '.NEXTVAL FROM DUAL' INTO t_num;

                    t_num := -t_num;

                    t_sql := 'ALTER SEQUENCE ' || v_seq_name || ' INCREMENT BY ' || t_num;
                    EXECUTE IMMEDIATE t_sql;

                    EXECUTE IMMEDIATE 'SELECT ' || v_seq_name || '.NEXTVAL FROM DUAL' INTO t_num;

                    t_sql := 'ALTER SEQUENCE ' || v_seq_name || ' INCREMENT BY 1';
                    EXECUTE IMMEDIATE t_sql;
                END IF;

            ELSE
                -- 序列不存在则授权并创建序列(需要既定权限，否则无权限授权)
                -- EXECUTE IMMEDIATE 'GRANT CREATE SEQUENCE TO scott';
                t_sql := 'CREATE SEQUENCE ' || v_seq_name ||
                         ' MINVALUE ' || v_min_value ||
                         ' MAXVALUE ' || v_max_value ||
                         ' START WITH ' || v_min_value ||
                         ' INCREMENT BY ' || v_increment_by ||
                         ' CACHE ' || v_cache;
                -- 根据表字段判断顶格是否循环重头开始
                IF v_cycle = 'Y' THEN
                    t_sql := t_sql || ' CYCLE';
                END IF;
                -- 执行创建序列
                EXECUTE IMMEDIATE t_sql;
            END IF;
        END LOOP;
END;
/

